package com.serviceprovider.business.module.sp.transaction.services.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.serviceprovider.business.module.admin.transaction.valueobjects.AdminTransactionCriteria;
import com.serviceprovider.business.module.admin.user.dao.interfaces.IUserDao;
import com.serviceprovider.business.module.common.Page;
import com.serviceprovider.business.module.common.PaginationHelper;
import com.serviceprovider.business.module.order.model.Order;
import com.serviceprovider.business.module.order.services.dao.interfaces.IOrderDao;
import com.serviceprovider.business.module.sp.transaction.model.Batch;
import com.serviceprovider.business.module.sp.transaction.model.TransactionCriteria;
import com.serviceprovider.business.module.sp.transaction.model.TransactionDetail;
import com.serviceprovider.business.module.sp.transaction.model.TransactionStatus;
import com.serviceprovider.business.module.sp.transaction.model.TransactionUserAmount;
import com.serviceprovider.business.module.sp.transaction.services.dao.interfaces.IBatchDao;
import com.serviceprovider.business.module.sp.transaction.services.dao.interfaces.ITransStatusDao;
import com.serviceprovider.business.module.sp.transaction.services.dao.interfaces.ITransactionDao;
import com.serviceprovider.business.module.sp.transaction.services.dao.interfaces.ITransactionUserAmountDao;
import com.serviceprovider.business.module.user.model.impl.User;
import com.serviceprovider.business.utility.maintenance.settings.AppSettings;

@Repository("transactionDao")
public class TransactionDaoImpl extends SimpleJdbcDaoSupport implements ITransactionDao {

    private TransactionRowMapper transactionMapper = new TransactionRowMapper();

    protected final Log logger = LogFactory.getLog(getClass());

    @Autowired
    private IUserDao userDao;
    @Autowired
    @Qualifier("transactionJdbcInsert")
    private SimpleJdbcInsert jdbcInsert;
    @Autowired
    private ITransStatusDao transStatusDao;
    @Autowired
    private IBatchDao batchDao;
    @Autowired
    private ITransactionUserAmountDao transactionUserAmountDao;
    @Autowired
    private IOrderDao orderDao;

    private TransStatusMapper transactionStatusRowMapper = new TransStatusMapper();

    @Autowired
    public TransactionDaoImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    private class TransactionRowMapper implements ParameterizedRowMapper<TransactionDetail> {

        @Override
        public TransactionDetail mapRow(ResultSet rs, int arg1) throws SQLException {
            TransactionDetail transaction = new TransactionDetail();
            transaction.setAmount(rs.getInt("transaction_amount"));
            transaction.setCustomerBalance(rs.getInt("customer_balance"));
            transaction.setCustomerSSN(rs.getInt("customer_ssn"));
            transaction.setCustomLabel1(rs.getInt("custom_label_1_value"));
            transaction.setCustomLabel2(rs.getInt("custom_label_2_value"));
            transaction.setDataLoadDate(rs.getDate("data_load_date"));
            transaction.setElectronicFillingFee(rs.getInt("electronic_filling_fee"));
            transaction.setElectronicPrepFee(rs.getInt("electronic_prep_fee"));
            transaction.setFedBankProductFee(rs.getInt("fed_bank_product_fee"));
            transaction.setId(rs.getInt("transaction_id"));
            transaction.setOriginatorName(rs.getString("originator_name"));
            transaction.setCustomerName(rs.getString("customer_name"));
            transaction.setSelfFee(rs.getInt("self_fee"));
            transaction.setServiceBureauFee(rs.getInt("service_bureau_fee"));
            transaction.setStateProductFee(rs.getInt("state_product_fee"));
            transaction.setTaxPrepFee(rs.getInt("tax_prep_fee"));
            transaction.setTraceNumber(rs.getString("trace_number"));
            transaction.setUserId(rs.getInt("user_id"));
            transaction.setServiceFee(rs.getInt("service_fee"));
            TransactionStatus status = transactionStatusRowMapper.mapRow(rs, arg1);
            Batch batch = batchDao.findBatchById(rs.getInt("batch_id"));
            List<TransactionUserAmount> amounts = transactionUserAmountDao.findByTransactionDetail(batch, transaction);

            transaction.setTransactionStatus(status);
            transaction.setTransactionUserAmounts(amounts);
            transaction.setBatch(batch);
            return transaction;
        }

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TransactionDetail> findTransactionsByUserId(int providerId) {
        String sql = "SELECT * FROM transaction_details t,transaction_status status,batch b WHERE (t.transaction_status_id=status.id) and (t.batch_id=b.batch_id) and t.user_id=" + providerId;
        return getSimpleJdbcTemplate().query(sql, transactionMapper);
    }

    @Override
    public TransactionDetail getTransactionById(int id) {
        String sql = "SELECT * FROM transaction_details t,transaction_status status,batch b WHERE (t.transaction_status_id=status.id) and (t.batch_id=b.batch_id) and t.transaction_id=" + id;
        try {
            return (TransactionDetail) getJdbcTemplate().queryForObject(sql, transactionMapper);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TransactionDetail> findTransactionsByBatch(int batchId) {
        String sql = "SELECT * FROM transaction_details t,transaction_status status,batch b WHERE (t.transaction_status_id=status.id) and (t.batch_id=b.batch_id) and t.batch_id=" + batchId;
        return getSimpleJdbcTemplate().query(sql, transactionMapper);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TransactionDetail> findTransactionsByPayee(int payeeid) {
        String sql = "SELECT distinct * FROM transaction_details t,transaction_status status,transaction_user_amount p,batch b "
                + "WHERE (t.transaction_id=p.transaction_id) and (t.transaction_status_id=status.id) and (t.batch_id=b.batch_id) and p.user_id=" + payeeid;
        return getSimpleJdbcTemplate().query(sql, transactionMapper);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> findPayeeListOnTransaction(TransactionDetail transaction) {
        String sql = "SELECT user_id FROM transaction_user_amount WHERE transaction_id=" + transaction.getId();
        List<Integer> userIds = getJdbcTemplate().query(sql, new RowMapper() {

            @Override
            public Object mapRow(ResultSet rs, int arg1) throws SQLException {
                return rs.getInt("user_id");
            }

        });
        return userDao.findUsersById(userIds);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TransactionDetail> findByProviderAndBatch(int providerId, int batchId) {
        String sql = "SELECT * FROM transaction_details t,transaction_status status,batch b WHERE (t.transaction_status_id=status.id) and (t.batch_id=b.batch_id) and t.batch_id=" + batchId + " and t.user_id=" + providerId;
        return getSimpleJdbcTemplate().query(sql, transactionMapper);
    }

    @Override
    public TransactionDetail insertTransaction(TransactionDetail t) {
        Map<String, Object> para = new HashMap<String, Object>();
        para.put("user_id", t.getUserId());
        para.put("batch_id", t.getBatch() == null ? null : t.getBatch().getId());
        para.put("trace_number", t.getTraceNumber() == null ? "" : t.getTraceNumber());
        para.put("transaction_status_id", t.getTransactionStatus().getId());
        para.put("transaction_amount", t.getAmount());
        para.put("customer_name", t.getCustomerName() == null ? "" : t.getCustomerName());
        para.put("originator_name", t.getOriginatorName() == null ? "" : t.getOriginatorName());
        para.put("customer_ssn", t.getCustomerSSN());
        para.put("self_fee", t.getSelfFee());
        para.put("tax_prep_fee", t.getTaxPrepFee());
        para.put("electronic_prep_fee", t.getElectronicPrepFee());
        para.put("electronic_filling_fee", t.getElectronicFillingFee());
        para.put("service_bureau_fee", t.getServiceBureauFee());
        para.put("fed_bank_product_fee", t.getFedBankProductFee());
        para.put("state_product_fee", t.getStateProductFee());
        para.put("custom_label_1_value", t.getCustomLabel1());
        para.put("custom_label_2_value", t.getCustomLabel2());
        para.put("customer_balance", t.getCustomerBalance());
        para.put("data_load_date", t.getDataLoadDate());
        Number id = jdbcInsert.executeAndReturnKey(para);
        t.setId(id.intValue());
        return t;
    }

    @Override
    public void deleteTransaction(int transactionId) {
        String sql = "DELETE FROM transaction_details WHERE transaction_id = ?";
        getSimpleJdbcTemplate().update(sql, transactionId);
    }

    @Override
    public void updateTransaction(TransactionDetail t) {
        String sql = "UPDATE transaction_details "
                + "SET user_id=?, batch_id=?, trace_number=?, transaction_status_id=?, transaction_amount=?, customer_name=?, originator_name=?, customer_ssn=?, self_fee=?, tax_prep_fee=?, electronic_prep_fee=?, electronic_filling_fee=?, service_bureau_fee=?, fed_bank_product_fee=?, state_product_fee=?, custom_label_1_value=?, custom_label_2_value=?, customer_balance=?, data_load_date=?, service_fee=? "
                + "WHERE transaction_id=?";
        Integer batchId = (t.getBatch() == null) ? null : t.getBatch().getId();
        getSimpleJdbcTemplate().update(
                sql,
                t.getUserId(), batchId, t.getTraceNumber(), t.getTransactionStatus().getId(), t.getAmount(),
                t.getCustomerName(), t.getOriginatorName(), t.getCustomerSSN(), t.getSelfFee(), t.getTaxPrepFee(),
                t.getElectronicPrepFee(), t.getElectronicFillingFee(), t.getServiceBureauFee(), t.getFedBankProductFee(),
                t.getStateProductFee(), t.getCustomLabel1(), t.getCustomLabel2(), t.getCustomerBalance(), t.getDataLoadDate(),
                t.getServiceFee(), t.getId());
    }

    @Override
    public void updateTransactionStatus(int transactionId, int transactionStatusId) {
        String sql = "UPDATE transaction_details SET transaction_status_id=? WHERE transaction_id=?";
        getSimpleJdbcTemplate().update(sql, transactionStatusId, transactionId);
    }

    
    @Override
    public void updateTransactionSelfFee(int transactionId, int selfFee) {
        String sql = "UPDATE transaction_details SET self_fee=? WHERE transaction_id=?";
        getSimpleJdbcTemplate().update(sql, selfFee, transactionId);
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<TransactionDetail> findNumberOfTransactionsInABatch(int batchId) {
        String sql = "SELECT * FROM transaction_details t,transaction_status status,batch b WHERE (t.transaction_status_id=status.id) and (t.batch_id=b.batch_id) and t.batch_id=" + batchId;
        return getSimpleJdbcTemplate().query(sql, transactionMapper);
    }

    @Override
    public Page<TransactionDetail> find(int pageNumber, int pageSize, String sortColumn, String sortOrder) {
        PaginationHelper<TransactionDetail> ph = new PaginationHelper<TransactionDetail>();
        String sqlCount = "SELECT count(*) FROM transaction_details";
        String sqlQuery = "SELECT * FROM transaction_details t,transaction_status status,batch b WHERE (t.transaction_status_id=status.id) and (t.batch_id=b.batch_id) order by " + sortColumn + " " + sortOrder;
        Object[] args = {};
        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args, pageNumber, pageSize, sortColumn, sortOrder, transactionMapper);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TransactionDetail> findByCriteria(TransactionCriteria criteria) {
        Date from = criteria.getDateFrom();
        Date to = criteria.getDateTo();
        Integer providerId = criteria.getProviderId();
        Integer transStatusId = criteria.getTransStatusId();
        List<Integer> payeesId = criteria.getPayeesId();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        boolean hasPayeesCriteria = (payeesId != null && payeesId.size() > 0);
        boolean hasOrderCriteria = (from != null || to != null);

        // At default, transaction details will be joined with batch table
        StringBuilder fromSql = new StringBuilder("transaction_details td,batch b");
        StringBuilder whereSql = new StringBuilder("(td.batch_id=b.batch_id)");
        String joinWord = " and ";

        if (hasPayeesCriteria) {
            fromSql.append(",transaction_user_amount tu");
            whereSql.append(joinWord).append("(td.transaction_id=tu.transaction_id)");
        }
        if (hasOrderCriteria) {
            fromSql.append(",order_details o");
            whereSql.append(joinWord).append("(b.order_id=o.order_id)");
        }

        if (from != null) {
            whereSql.append(joinWord).append("(o.date >= ?)");
            params.add(from);
        }
        if (to != null) {
            whereSql.append(joinWord).append("(o.date <= ?)");
            params.add(to);
        }
        if (providerId != null) {
            whereSql.append(joinWord).append("(td.user_id = ?)");
            params.add(providerId);
        }
        if (transStatusId != null && transStatusId > 0) {
            whereSql.append(joinWord).append("(td.transaction_status_id = ?)");
            params.add(transStatusId);
        }
        if (hasPayeesCriteria) {
            whereSql.append(joinWord).append("(tu.user_id in (");
            String inJoin = "";
            for (Integer payeeId : payeesId) {
                whereSql.append(inJoin).append(payeeId);
                inJoin = ",";
            }
            whereSql.append("))");
        }
        sql.append("SELECT * FROM ").append(fromSql).append(" WHERE ").append(whereSql);
        if (logger.isDebugEnabled()) {
            logger.debug(sql);
            if (params.size() > 0) {
                logger.debug(params);
            }
        }
        List<TransactionDetail> query = getJdbcTemplate().query(sql.toString(), params.toArray(), transactionMapper);
        Set<Integer> existedId = new HashSet<Integer>();
        List<TransactionDetail> distinctResult = new ArrayList<TransactionDetail>();
        for (TransactionDetail transactionDetail : query) {
            if (!existedId.contains(transactionDetail.getId())) {
                existedId.add(transactionDetail.getId());
                distinctResult.add(transactionDetail);
            }
        }
        return distinctResult;
    }

    @Override
    public List<TransactionDetail> findNewTransactions(int providerId) {
        String sql = "SELECT * FROM transaction_details t WHERE (t.transaction_status_id=?) and t.user_id=?";
        return getSimpleJdbcTemplate().query(sql, transactionMapper, AppSettings.TRANSACTION_STATUS_NEW_ID, providerId);
    }

    @Override
    public List<TransactionDetail> findTransactionsbyStatus(int statusid, int providerId) {
        String sql = "SELECT * FROM transaction_details t WHERE (t.transaction_status_id=?) and t.user_id=?";
        return getSimpleJdbcTemplate().query(sql, transactionMapper, statusid, providerId);
    }
    
    @Override
    public void moveTransactionsToNew(List<Integer> ids) {
        for (Integer batchId : ids) {
            List<TransactionDetail> transactions = findTransactionsByBatch(batchId);
            if (transactions != null) {
                for (TransactionDetail tr : transactions) {
                    tr.setSelfFee(null);
                    tr.setCustomLabel1(null);
                    tr.setCustomLabel2(null);
                    tr.setElectronicFillingFee(null);
                    tr.setFedBankProductFee(null);
                    tr.setServiceBureauFee(null);
                    tr.setTaxPrepFee(null);
                    tr.setCustomerBalance(null);
                    tr.setServiceFee(null);
                    tr.setElectronicPrepFee(null);
                    tr.setTransactionStatus(transStatusDao.get(AppSettings.TRANSACTION_STATUS_NEW_ID));
                    tr.setBatch(null);
                    updateTransaction(tr);
                }
            }
        }
        List<Integer> deletedBatchId = new ArrayList<Integer>();
        for (Integer batchId : ids) {
            int count = countByBatchId(batchId);
            if (count == 0) {
                deletedBatchId.add(batchId);
            }
        }
        if (!deletedBatchId.isEmpty()) {
            orderDao.deleteByBatchId(deletedBatchId);
            transactionUserAmountDao.deleteByBatchId(deletedBatchId);
            batchDao.deleteBatch(deletedBatchId);
        }
    }
    
    private TransactionDetail getTransactionDetailById(Integer id){
    	 String sql = "SELECT * FROM transaction_details t WHERE t.transaction_id=" + id;
         try {
             return (TransactionDetail) getJdbcTemplate().queryForObject(sql, transactionMapper);
         } catch (EmptyResultDataAccessException e) {
             return null;
         }
    }
    
    @Override
    public void moveTransactionsToNewStatus(List<Integer> ids) {
        for (Integer transactionId : ids) {
            TransactionDetail tr = getTransactionDetailById(transactionId);
            List<TransactionDetail> dependencyTransactions =null;
            Batch batch = null;
            if (tr != null) {
                    tr.setSelfFee(null);
                    tr.setCustomLabel1(null);
                    tr.setCustomLabel2(null);
                    tr.setElectronicFillingFee(null);
                    tr.setFedBankProductFee(null);
                    tr.setServiceBureauFee(null);
                    tr.setTaxPrepFee(null);
                    tr.setCustomerBalance(null);
                    tr.setServiceFee(null);
                    tr.setElectronicPrepFee(null);
                    tr.setTransactionStatus(transStatusDao.get(AppSettings.TRANSACTION_STATUS_NEW_ID));
                    batch = tr.getBatch();
                    tr.setBatch(null);
                    updateTransaction(tr);
                   
                    // batch should be deleted if no other transaction is associated with it.
                    if(batch!=null){
                    	dependencyTransactions = findTransactionsByBatch(batch.getId());
                    	if(dependencyTransactions.size()==0){
                    		/*// find dependency order.
                    		Integer orderId = batch.getOrderId();
                    		if(orderId!=null){
                    			// find any dependency batch
                    			List<Batch> dependencyBatch = batchDao.findBatchByOrder(orderId);
                    			if(dependencyBatch==null || dependencyBatch.size()==0){
                    				
                    			}
                    		}*/
                    		// delete batch
                    		batchDao.deleteBatch(batch.getId());
                    	}
                    }
            }
        }
        /*List<Integer> deletedBatchId = new ArrayList<Integer>();
        for (Integer batchId : ids) {
            int count = countByBatchId(batchId);
            if (count == 0) {
                deletedBatchId.add(batchId);
            }
        }
        if (!deletedBatchId.isEmpty()) {
            orderDao.deleteByBatchId(deletedBatchId);
            transactionUserAmountDao.deleteByBatchId(deletedBatchId);
            
        }*/
    }
    

    @Override
    public int countByBatchId(Integer id) {
        String sql = String.format("select count(*) from transaction_details where batch_id=%d", id);
        return getSimpleJdbcTemplate().queryForInt(sql);
    }

        @Override
        public void updateTransactaionCustomerBalance(int transactionId, int customerBalance) {
                String sql = "UPDATE transaction_details SET customer_balance=? WHERE transaction_id=?";
        getSimpleJdbcTemplate().update(sql, customerBalance, transactionId);
        }

        @Override
        public List<TransactionDetail> findByAdminCriteria(
                        AdminTransactionCriteria criteria) {
                StringBuffer sql = new StringBuffer("SELECT * FROM transaction_details t INNER JOIN user u ON t.user_id = u.id WHERE ");
                String condition = " AND";
                String temp = "";
                if(criteria.getProviderId()!=null){
                        sql.append(" t.user_id="+criteria.getProviderId()).append(condition);
                }
                if(criteria.getTraceId()!=null){
                        sql.append(" t.trace_number="+criteria.getTraceId()).append(condition);
                }
                if(criteria.getTransactionId()!=null){
                        sql.append(" t.transaction_id="+criteria.getTransactionId()).append(condition);
                }
                if(criteria.getProviderSSN()!=null){
                        sql.append(" u.ssn="+criteria.getProviderSSN()).append(condition);
                }
                if(criteria.getCustomerSSN()!=null){
                        sql.append(" t.customer_ssn="+criteria.getCustomerSSN()).append(condition);
                }
                if(criteria.getStatus()!=null){
                        sql.append(" t.transaction_status_id="+criteria.getStatus()).append(condition);
                }
                temp = criteria.getProviderFirstName();
                if(temp!=null && !temp.isEmpty()){
                        sql.append(" u.firstName LIKE '%"+temp).append("%' ").append(condition);
                }
                temp = criteria.getProviderLastName();
                if(temp!=null && !temp.isEmpty()){
                        sql.append(" u.lastName LIKE '%"+temp).append("%' ").append(condition);
                }
                String tempSQL = sql.toString().trim();
                if(tempSQL.endsWith("AND"))
                        sql.delete(sql.length()-3, sql.length());
                
                if(logger.isDebugEnabled())
                        logger.debug("SQL search for admin: "+sql);
                
                @SuppressWarnings("unchecked")
                List<TransactionDetail> list = getJdbcTemplate().query(sql.toString(), transactionMapper);
                
                
                return list;
        }

        @Override
        public void moveTransactionToProvider(int transactionId, int providerId) {
                Integer status = AppSettings.TRANSACTION_STATUS_NEEDING_MATCH;
                String sql = "UPDATE transaction_details SET user_id=?,transaction_status_id=? WHERE transaction_id=?";
        getSimpleJdbcTemplate().update(sql, providerId, status, transactionId);
        }

        @Override
        public List<TransactionDetail> getMatchedTransactionDetails(Integer transactionId, Integer customerSSN) {
		String sql="SELECT * from transaction_details details,transaction_status status where customer_ssn=? and status.id=details.transaction_status_id";
		int arg = 0;
		if(customerSSN==null)
		{
			sql = "SELECT * from transaction_details details,transaction_status status where trace_number=? and status.id=details.transaction_status_id";
			arg = transactionId.intValue();
		}
		else
			arg = customerSSN.intValue();
		
		return getSimpleJdbcTemplate().query(sql, transactionMapper,arg);		
	}
	
}